package com.example.blogapi.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseManager {
    private static final String DB_URL = "jdbc:sqlite:db/blog.db";

    static {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("SQLite JDBC driver not found", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        // 每次都创建新的连接，避免连接被关闭后再使用的问题
        return DriverManager.getConnection(DB_URL);
    }

    public static void initDatabase() {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            
            // 创建用户表
            stmt.execute("CREATE TABLE IF NOT EXISTS users (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "username TEXT NOT NULL UNIQUE," +
                    "email TEXT NOT NULL UNIQUE," +
                    "password TEXT NOT NULL," +
                    "created_at TEXT NOT NULL" +
                    ")");

            // 创建分类表
            stmt.execute("CREATE TABLE IF NOT EXISTS categories (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "name TEXT NOT NULL UNIQUE," +
                    "description TEXT," +
                    "created_at TEXT NOT NULL" +
                    ")");

            // 创建标签表
            stmt.execute("CREATE TABLE IF NOT EXISTS tags (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "name TEXT NOT NULL UNIQUE," +
                    "created_at TEXT NOT NULL" +
                    ")");

            // 创建博客表
            stmt.execute("CREATE TABLE IF NOT EXISTS blogs (" +
                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "title TEXT NOT NULL," +
                    "content TEXT NOT NULL," +
                    "user_id INTEGER NOT NULL," +
                    "category_id INTEGER NOT NULL," +
                    "created_at TEXT NOT NULL," +
                    "FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE," +
                    "FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE" +
                    ")");

            // 创建博客标签关联表
            stmt.execute("CREATE TABLE IF NOT EXISTS blog_tags (" +
                    "blog_id INTEGER NOT NULL," +
                    "tag_id INTEGER NOT NULL," +
                    "PRIMARY KEY (blog_id, tag_id)," +
                    "FOREIGN KEY (blog_id) REFERENCES blogs(id) ON DELETE CASCADE," +
                    "FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE" +
                    ")");

            System.out.println("Database initialized successfully");
        } catch (SQLException e) {
            System.err.println("Error initializing database: " + e.getMessage());
            throw new RuntimeException("Error initializing database", e);
        }
    }

    public static void closeConnection() {
        // 不需要关闭全局连接，因为我们现在每次都创建新的连接
        // 每个连接在使用完毕后都会在try-with-resources中自动关闭
    }
}